/*
 * Copyright (c) 2006, 2022, webrx.cn All rights reserved.
 *
 */
package cn.webrx.db;

import org.junit.jupiter.api.AfterEach;
import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.Test;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.time.format.DateTimeFormatter;
import java.util.Properties;

/**
 * <p>Project: jdemo202203 - ResultSetDemo
 * <p>Powered by webrx On 2022-11-01 19:33:55
 *
 * @author webrx [webrx@126.com]
 * @version 1.0
 * @since 17
 */


public class ResultSetDemo {
    Connection conn = null;


    @Test
    void rs() {
        try {
            PreparedStatement pst = conn.prepareStatement("select id,score,name,gender,birth from t_student");
            ResultSet rs = pst.executeQuery();
            while (rs.next()) {
                //MySQL Date类型 对应 java LocalDate类型 都是只有日期
                String birth = rs.getDate("birth").toLocalDate().format(DateTimeFormatter.ofPattern("yyyy年MM月dd日"));
                //System.out.printf("%tF%n",rs.getDate("birth").toLocalDate());
                String info = String.format("编号：%d%n姓名：%s%n成绩：%d分%n性别：%s%n出生日期：%s%n", rs.getInt("id"), rs.getString("name"), rs.getInt("score"), rs.getString("gender"), birth);
                System.out.println();
                System.out.println(info);

            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @BeforeEach
    void connection() {
        try {
            InputStream is = Thread.currentThread().getContextClassLoader().getResourceAsStream("db.properties");
            Properties prop = new Properties();
            prop.load(is);
            String driver = prop.getProperty("db.driver", "com.mysql.cj.jdbc.Driver");
            String url = prop.getProperty("db.url", "jdbc:mysql:/");
            String user = prop.getProperty("db.username", "root");
            String password = prop.getProperty("db.password", "");

            Class.forName(driver);
            conn = DriverManager.getConnection(url, user, password);


        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @AfterEach
    void close() {
        try {
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @Test
    void show() {
        try {
            //Statement st = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
            Statement st = conn.createStatement();
            //st.execute("create database if not exists `db` default character set utf8");
            //st.execute("create table t_student(id int unsigned auto_increment,name varchar(50),primary key(id))");
            //int rows = st.executeUpdate("insert into t_student(name) values('李四'),('张三'),('王五'),('张三丰')");
            //System.out.println(rows);

            ResultSet rs = st.executeQuery("select id,name from t_student where name like '周%' order by id desc");
            //System.out.println(rs.isBeforeFirst());

            //rs.next(); //将游标下移，如果没有结果，返回false,如果不能下称也返回false,如果能下称就返回true;
            //System.out.println(rs.getInt("id"));
            //System.out.println(rs.getString("name"));
            //
            //rs.next();
            //rs.previous();
            //System.out.println(rs.getInt("id"));
            //System.out.println(rs.getString("name"));
            //
            //rs.first();
            //System.out.println(rs.getInt("id"));
            //System.out.println(rs.getString("name"));
            //
            //rs.absolute(3);
            //System.out.println(rs.getInt("id"));
            //System.out.println(rs.getString("name"));


            if (rs.isBeforeFirst()) {
                while (rs.next()) {
                    System.out.println(rs.getInt("id"));
                    System.out.println(rs.getString("name"));
                }
            } else {
                System.out.println("查询无结果");
            }


            //判断游标
            //rs.isBeforeFirst()  如果有结果，游标就在这个位置，返回true 返回false就是没有数据
            //rs.isFirst()
            //rs.isLast()
            //rs.isAfterLast()

            //控制游标位置，移动位置
            //rs.next();
            //rs.absolute(6);
            //rs.previous();
            //rs.first();
            //rs.last();
            //rs.beforeFirst();
            //rs.afterLast();


            //循环遍历查询结果集
            //while(rs.next()){
            //    System.out.println(rs.getInt(1));
            //    System.out.println(rs.getString("name"));
            //}

            //rs = st.executeQuery("select @@version,now()");
            //rs.next();
            //System.out.println(rs.getString(1));
            //System.out.println(rs.getString(2));
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}
